/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import entities.Groups;
import entities.Member;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author lion
 */
public class MemberDao {

    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    MysqlHelper mysql = new MysqlHelper();

    public boolean login(Member member) throws Exception {
        conn = mysql.GetConnection();
        ps = conn.prepareStatement("SELECT * FROM fl_member WHERE usename=? AND passwd = ?");
        ps.setString(1, member.getUsername());
        ps.setString(2, member.getPassword());
        if (ps.executeQuery().next()) {
            return true;
        }
        return false;
    }

    public void register(Member member) throws Exception {
        conn = mysql.GetConnection();
        ps = conn.prepareStatement(
                "INSERT INTO fl_member VALUES(DEFAULT,?,?,?,?,?)");
        ps.setString(1, member.getUsername());
        ps.setString(2, member.getPassword());
        ps.setString(3, member.getEmail());
        ps.setBoolean(4, false);
        ps.setString(5, member.getImage());
        ps.executeUpdate();

        MemberGroupDao mgd = new MemberGroupDao();
        Groups g = new Groups();
        g.setGroupname("Student");
        g.setGroupid(new GroupsDao().getGroupId(g));
        member.setMemberid(getMemberIdByUsername(member));
        mgd.addToMemberGroup(g, member);
    }

    public String retrievePassword(Member member) throws Exception {
        String password = "";
        try {
            conn = mysql.GetConnection();
            ps = conn.prepareStatement(
                    "SELECT * FROM fl_member WHERE usename=? AND email=?");
            ps.setString(1, member.getUsername());
            ps.setString(2, member.getEmail());
            rs = ps.executeQuery();
            while (rs.next()) {
                password = rs.getString("passwd");
            }
        } catch (Exception e) {
            throw e;
        }
        return password;
    }

    public void changePassword(Member member, String newPassword) throws Exception {
        int id = getMemberIdByUsername(member);
        conn = mysql.GetConnection();
        ps = conn.prepareStatement("UPDATE fl_member SET `passwd` = ? WHERE `memberid`=?");
        ps.setString(1, newPassword);//member.getPassword());
        ps.setInt(2, id);
        ps.executeUpdate();
    }

    public boolean checkExist(Member member) throws Exception {
        conn = mysql.GetConnection();
        rs = mysql.ExecuteReader("SELECT * FROM fl_member");
        while (rs.next()) {
            if (rs.getString("usename").equals(member.getUsername())
                    || rs.getString("email").equals(member.getEmail())) {
                return true;
            }
        }
        return false;
    }

    public boolean checkInformation(Member member) throws Exception {
        conn = mysql.GetConnection();
        ps = conn.prepareStatement(
                "SELECT * FROM fl_member WHERE usename=? AND passwd=? AND email=?");
        ps.setString(1, member.getUsername());
        ps.setString(2, member.getPassword());
        ps.setString(3, member.getEmail());
        rs = ps.executeQuery();
        if (rs.next()) {
            return true;
        }
        return false;
    }

    public boolean isFaculty(Member member) throws Exception {
        Groups groups = new Groups();
        groups.setGroupname("Faculty");
        return findGroup(member, groups);
    }

    public boolean isAdmin(Member member) throws Exception {
        Groups groups = new Groups();
        groups.setGroupname("Administrator");
        return findGroup(member, groups);
    }

    public boolean isStudent(Member member) throws Exception {
        Groups groups = new Groups();
        groups.setGroupname("Student");
        return findGroup(member, groups);
    }

    private boolean findGroup(Member member, Groups groups) throws Exception {
        boolean flag = false;
        conn = mysql.GetConnection();
        int memberid = getMemberIdByUsername(member);
        int groupid = new GroupsDao().getGroupId(groups);
        ps = conn.prepareStatement("SELECT * FROM fl_membergroup WHERE memberid=?");
        ps.setInt(1, memberid);
        rs = ps.executeQuery();
        while (rs.next()) {
            if (rs.getInt("groupid") == groupid) {
                flag = true;
            }
        }
        return flag;
    }

    public boolean isBlock(Member member) throws Exception {
        boolean block = false;
        conn = mysql.GetConnection();
        ps = conn.prepareStatement("SELECT * FROM fl_member WHERE memberid=?");
        ps.setInt(1, getMemberIdByUsername(member));
        rs = ps.executeQuery();
        while (rs.next()) {
            block = rs.getBoolean("block");
        }
        return block;
    }

    public int getMemberIdByUsername(Member member) throws Exception {
        int id = 0;
        try {
            conn = mysql.GetConnection();
            ps = conn.prepareStatement("SELECT * FROM fl_member WHERE usename=?");
            ps.setString(1, member.getUsername());
            rs = ps.executeQuery();
            while (rs.next()) {
                id = rs.getInt("memberid");
            }
        } catch (Exception e) {
            throw e;
        }
        return id;
    }

    public String getUsernameByID(int memberid) throws Exception {
        String username = "";
        try {
            conn = mysql.GetConnection();
            ps = conn.prepareStatement("SELECT * FROM fl_member WHERE memberid=?");
            ps.setInt(1, memberid);
            rs = ps.executeQuery();
            while (rs.next()) {
                username = rs.getString("usename");
            }
        } catch (Exception e) {
            throw e;
        }
        return username;
    }

    public ArrayList<Member> getMemberList() throws Exception {
        ArrayList<Member> members = new ArrayList<Member>();
        try {
            conn = mysql.GetConnection();
            rs = mysql.ExecuteReader("SELECT * FROM fl_member");
            while (rs.next()) {
                Member member = new Member();
                member.setUsername(rs.getString("usename"));
                member.setMemberid(rs.getInt("memberid"));
                members.add(member);
            }
        } catch (Exception e) {
            throw e;
        }
        return members;
    }
        
    public void blockMember(int memberid) throws Exception{
        conn = mysql.GetConnection();
        ps = conn.prepareStatement("UPDATE fl_member SET block=? WHERE memberid =?");
        ps.setBoolean(1, true);
        ps.setInt(2, memberid);
        ps.executeUpdate();
    }
    
    public void logout() throws Exception {
        conn = mysql.GetConnection();
        mysql.Close();
    }
}
